/**
 * 
 */
package server.db;

import java.io.IOException;
import java.util.Date;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import org.apache.log4j.Logger;

import server.config.ServerConfig;
import server.container.AlarmierteSchleife;
import server.container.EinsatzAlarm;
import server.container.JProwlInfo;
import server.container.NotifryInfo;
import server.container.SMSAlamierung;

/**
 * @author Christian
 * 
 */
public class SQL {

	private static Logger logger = Logger.getLogger(SQL.class);

	private static ConnectionPool conPool = null;

	private static SQL sql = null;

	private ServerConfig config = ServerConfig.getInstance();

	/**
	 * 
	 * @throws Exception
	 */
	private SQL() throws IOException {

		if (conPool == null) {
			conPool = new ConnectionPool("jdbc:mysql://" + config.getDbHost() + "/"
					+ config.getDbName(), config.getDbUserName(), config.getDbPassword());
		}
	}

	// private Connection getCon() {
	//
	// logger.trace("getCon()");
	// try {
	// if (con == null || con.isClosed() || !con.isValid(10)) {
	// Class.forName("com.mysql.jdbc.Driver");
	// String connectionUrl = + "?" + "user=" +
	// + "&password=" + + "";
	// con = DriverManager.getConnection(connectionUrl);
	// // con test
	// if (!con.isValid(10)) {
	// logger.debug("SQL Verbindung aufgebaut fehlgeschlagen");
	// } else {
	// logger.debug("SQL Verbindung aufgebaut");
	// }
	// }
	// } catch (Exception e) {
	// logger.error("SQL Verbindung nicht aufgebaut", e);
	// throw new RuntimeException(e);
	// }
	// return con;
	// }

	/**
	 * 
	 * @return
	 */
	public static SQL getInstance() {

		if (sql == null) {
			logger.trace("getInstance()");
			try {
				sql = new SQL();
			} catch (Exception e) {
				logger.error("getInstance()", e);
				throw new RuntimeException(e);
			}
		}
		return sql;
	}

	/**
	 * 
	 * @param schleife
	 * @return
	 * @throws SQLException
	 */
	public List<SMSAlamierung> getSMSAlarmList(EinsatzAlarm alarm) {

		logger.trace("getNummernBySchleife()");
		List<SMSAlamierung> list = new ArrayList<SMSAlamierung>();
		String sql = "SELECT user.id as id, user.kosten as kosten, user.bezahlt as bezahlt,"
				+ "zugehoerigkeit.statusJ as statusJ, zugehoerigkeit.statusE as statusE, zugehoerigkeit.statusF as statusF, zugehoerigkeit.statusP as statusP, "
				+ "zugehoerigkeit.statusH as statusH, zugehoerigkeit.statusC as statusC, zugehoerigkeit.montag as montag, zugehoerigkeit.dienstag as dienstag, "
				+ "zugehoerigkeit.mittwoch as mittwoch, zugehoerigkeit.donnerstag as donnerstag, zugehoerigkeit.freitag as freitag, zugehoerigkeit.samstag as samstag, "
				+ "zugehoerigkeit.sonntag as sonntag, zugehoerigkeit.vonstunde as vonstunde, zugehoerigkeit.vonminute as vonminute, zugehoerigkeit.bisstunde as bisstunde, "
				+ "zugehoerigkeit.bisminute as bisminute, zugehoerigkeit.statusA as statusA, zugehoerigkeit.einsatz5ton as einsatz5ton,"
				+ " user.absender as absender, user.nummer as nummer, zugehoerigkeit.smsmode as smsmode FROM feuerwehren JOIN user JOIN zugehoerigkeit  WHERE (feuerwehren.id = '"
				+ alarm.getFeuerwehrID()
				+ "' AND feuerwehren.id = zugehoerigkeit.feuerwehrid AND user.id = zugehoerigkeit.userid AND user.aktive = 1 AND user.nummerchecked)";
		Statement stmt = null;
		Connection con = null;
		try {
			con = conPool.getConnectionFromPool();
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {

				// statusfiltern
				if (alarm.getEinsatzArt().equals(EinsatzAlarm.PROBEALARM)) {
					if (!rs.getBoolean("statusJ")) {
						continue;
					}
				}
				if (alarm.getEinsatzArt().equals(EinsatzAlarm.BRANDEINSATZ)) {
					if (!rs.getBoolean("statusF")) {
						continue;
					}
				}
				if (alarm.getEinsatzArt().equals(EinsatzAlarm.PERSONENRETTUNG)) {
					if (!rs.getBoolean("statusP")) {
						continue;
					}
				}
				if (alarm.getEinsatzArt().equals(EinsatzAlarm.BMA)) {
					if (!rs.getBoolean("statusE")) {
						continue;
					}
				}
				if (alarm.getEinsatzArt().equals(EinsatzAlarm.GEFAHRGUTEINSATZ)) {
					if (!rs.getBoolean("statusC")) {
						continue;
					}
				}
				if (alarm.getEinsatzArt().equals(EinsatzAlarm.ANRUFEN)) {
					if (!rs.getBoolean("statusA")) {
						continue;
					}
				}
				if (alarm.getEinsatzArt().equals(EinsatzAlarm.EINSATZ5TON)) {
					if (!rs.getBoolean("einsatz5ton")) {
						continue;
					}
				}

				DateFormat dfmt = new SimpleDateFormat("E");
				String tag = dfmt.format(new Date(alarm.getAlarmTime())); // Sa
				DateFormat dfmt2 = new SimpleDateFormat("HH"); // "E.', den' dd.MM.yy 'um' hh:mm:ss"
				int stunde = Integer.parseInt(dfmt2.format(new Date(alarm.getAlarmTime())));
				DateFormat dfmt3 = new SimpleDateFormat("mm");
				int minute = Integer.parseInt(dfmt3.format(new Date(alarm.getAlarmTime())));
				if (tag.equals("Mo")) {
					if (!rs.getBoolean("montag")) {
						continue;
					}
				}
				if (tag.equals("Di")) {
					if (!rs.getBoolean("dienstag")) {
						continue;
					}
				}
				if (tag.equals("Mi")) {
					if (!rs.getBoolean("mittwoch")) {
						continue;
					}
				}
				if (tag.equals("Do")) {
					if (!rs.getBoolean("donnerstag")) {
						continue;
					}
				}
				if (tag.equals("Fr")) {
					if (!rs.getBoolean("freitag")) {
						continue;
					}
				}
				if (tag.equals("Sa")) {
					if (!rs.getBoolean("samstag")) {
						continue;
					}
				}
				if (tag.equals("So")) {
					if (!rs.getBoolean("sonntag")) {
						continue;
					}
				}
				if (rs.getInt("vonstunde") > stunde || stunde > rs.getInt("bisstunde")) {
					continue; // skip
				}
				if (rs.getInt("vonstunde") == stunde) {

					if (rs.getInt("vonminute") > minute) {
						continue; // skip
					}
				}

				if (rs.getInt("bisstunde") == stunde) {

					if (rs.getInt("bisminute") < minute) {
						continue; // skip
					}
				}

				/** FILTERNENDE */
				double kontoStand = rs.getDouble("bezahlt") - rs.getDouble("kosten");
				kontoStand = Math.round(kontoStand * 100) / 100.0;
				list.add(new SMSAlamierung(rs.getString("id"), rs.getString("nummer"), alarm, rs
						.getString("smsmode"), rs.getString("absender"), kontoStand + ""));
			}
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conPool.returnConnectionToPool(con);
		}
		logger.trace("Liste Sortieren");
		Collections.sort(list);
		logger.trace("doppelte entfernen");
		for (int i = 1; i < list.size(); i++) {
			if (list.get(i - 1).getNummer().equals(list.get(i).getNummer())) {
				list.remove(i - 1);
				i--;
			}
		}
		return list;
	}

	public void addKosten(String nummer, String kosten) {

		logger.trace("addKosten");
		// Steuer mitloggen
		kosten = (Double.parseDouble(kosten) * 1.19) + "";
		String sql = "UPDATE user SET kosten = kosten + '" + kosten + "' WHERE nummer = '" + nummer
				+ "'";
		Statement stmt;
		Connection con = null;
		try {
			con = conPool.getConnectionFromPool();
			stmt = con.createStatement();
			stmt.executeUpdate(sql);
			stmt.close();
		} catch (SQLException e) {
			logger.error("SQLException", e);
		}
		String sql2 = "SELECT id FROM user WHERE nummer = '" + nummer + "'";
		Statement stmt2;
		String userid = "";
		try {
			stmt2 = con.createStatement();
			ResultSet rs2 = stmt2.executeQuery(sql2);
			while (rs2.next()) {
				userid = rs2.getString("id");
			}
			stmt2.close();
		} catch (SQLException e) {
			logger.error("SQLException", e);
		}
		String sql3 = "INSERT INTO kosten SET user='" + userid + "', kosten = '" + kosten + "'";
		Statement stmt3 = null;
		try {
			stmt3 = con.createStatement();
			stmt3.executeUpdate(sql3);
			stmt3.close();
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt3.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conPool.returnConnectionToPool(con);
		}
	}

	public int alarmierungVorhanden(EinsatzAlarm alarm) {

		logger.trace("alarmierungVorhanden");

		String sql = "SELECT id FROM `alarmierungen` WHERE feuerwehrid='"
				+ alarm.getFeuerwehrID()
				+ "' AND UNIX_TIMESTAMP(timestamp) > UNIX_TIMESTAMP(now()) - 900 ORDER BY `timestamp` DESC LIMIT 1";
		Statement stmt1 = null;
		Connection con = null;
		try {
			con = conPool.getConnectionFromPool();
			stmt1 = con.createStatement();
			ResultSet rs = stmt1.executeQuery(sql);
			while (rs.next()) {

				return rs.getInt("id");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				stmt1.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conPool.returnConnectionToPool(con);
		}
		return -1;
	}

	public void alarmierungLoggen(final EinsatzAlarm alarm) {

		logger.trace("alarmierungLoggen");
		int alarmid = alarmierungVorhanden(alarm);
		boolean eintragen = false;
		boolean updateNoetig = false;
		// Neuealarme einfügen
		if (alarmid != -1 && alarm.getFeuerwehrID() == -1) {

			if (!isAlarmKennungSchonEingegangen(alarmid, alarm.getAlarmKennung())) {
				eintragen = true;
			}
		} else if (alarmid == -1) {
			eintragen = true;
		}

		if (eintragen) {

			PreparedStatement stmt = null;
			Connection con = null;
			try {
				con = conPool.getConnectionFromPool();
				stmt = con
						.prepareStatement("INSERT INTO `alarmierungen` SET feuerwehrid = ?, debug = ?");

				stmt.setInt(1, alarm.getFeuerwehrID());
				stmt.setBoolean(2, insertAsDebug(alarm.getFeuerwehrID()));
				stmt.executeUpdate();
			} catch (SQLException e) {
				logger.error("SQLException", e);
			} finally {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				conPool.returnConnectionToPool(con);
			}
			alarmid = alarmierungVorhanden(alarm);
			updateNoetig = true;

		}

		// Infos anfügen

//		String rictext = "";
//		if (alarm.isPocsag()) {
//			rictext = alarm.getRicText();
//		}

		if (!isAlarmKennungSchonEingegangen(alarmid, alarm.getAlarmKennung())) {
			PreparedStatement stmt1 = null;
			Connection con = null;
			try {
				con = conPool.getConnectionFromPool();
				stmt1 = con
						.prepareStatement("INSERT INTO `alarmierteschleifen` SET alarmid = ?, einsatzart = ?, alarmkennung = ?, status = ?, rictext = ?");

				stmt1.setInt(1, alarmid);
				stmt1.setString(2, alarm.getEinsatzArt());
				stmt1.setString(3, alarm.getAlarmKennung());
				stmt1.setString(4, alarm.getStatus());
				stmt1.setString(5, "");
				stmt1.executeUpdate();
			} catch (SQLException e) {
				logger.error("SQLException", e);
			} finally {
				try {
					stmt1.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				conPool.returnConnectionToPool(con);
			}
			updateNoetig = true;
		}
		if (updateNoetig) {
			Thread.yield();
			addAlarmShowTable(alarmid);
		}
	}

	/**
	 * 
	 * @param id
	 *            Feuerwehrid
	 * @return
	 */
	private boolean insertAsDebug(int id) {

		if (ServerConfig.getInstance().isDebug()) {

			return true;
		}
		// Testfeuerwehr
		if (id == 2) {
			return true;
		}
		return false;
	}

	public void addAlarmShowTable(int alarmid) {

//		long time1 = System.nanoTime();
		Connection con = null;
		PreparedStatement stmt1 = null;
		try {

			con = conPool.getConnectionFromPool();
			stmt1 = con.prepareStatement("DELETE FROM alarmierungscache WHERE alarmid = ?");

			stmt1.setInt(1, alarmid);
			stmt1.executeUpdate();
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt1.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conPool.returnConnectionToPool(con);
		}
		int feuerwehrId = 0;
		long alarmTimestamp = 0;
		String datum = "";
		String uhrzeit = "";
		String einsatzArt = "";
		String alarmKennung = "";
		String status = "";
		String rictext = "";
//		long time2 = System.nanoTime();
		PreparedStatement stmt = null;
		try {
			con = conPool.getConnectionFromPool();
			stmt = con
					.prepareStatement("SELECT feuerwehrid, UNIX_TIMESTAMP(`timestamp`) as timestamp FROM `alarmierungen` WHERE id = ? LIMIT 1");

			stmt.setInt(1, alarmid);
			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {

				feuerwehrId = rs.getInt("feuerwehrid");
				alarmTimestamp = rs.getLong("timestamp");
				alarmTimestamp *= 1000;
				DateFormat dfmt = new SimpleDateFormat("dd.MM.yy");
				datum = (dfmt.format(alarmTimestamp));
				DateFormat dfmt2 = new SimpleDateFormat("HH:mm:ss");
				uhrzeit = (dfmt2.format(alarmTimestamp));

			}
			stmt.close();
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conPool.returnConnectionToPool(con);
		}
//		long time3 = System.nanoTime();
		List<AlarmierteSchleife> list = new ArrayList<AlarmierteSchleife>();

		con = null;
		PreparedStatement stmt2 = null;
		try {
			con = conPool.getConnectionFromPool();
			stmt2 = con
					.prepareStatement("SELECT alarmkennung, status, einsatzart, rictext FROM `alarmierteschleifen` WHERE alarmid = ?");

			stmt2.setInt(1, alarmid);
			ResultSet rs = stmt2.executeQuery();

			while (rs.next()) {

				String alarmType = getAlarmType(rs.getString("alarmkennung"));
				list.add(new AlarmierteSchleife(alarmType, rs.getString("alarmkennung"), rs
						.getString("einsatzart"), rs.getString("rictext"), rs.getString("status")));

			}
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt2.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conPool.returnConnectionToPool(con);
		}
//		long time4 = System.nanoTime();
		// Liste sortieren
		Collections.sort(list);

		for (AlarmierteSchleife s : list) {

			if (einsatzArt.equals("")) {
				einsatzArt = s.getEinsatzart();
			}
			if (status.equals("") && !s.getStatus().equals("-1")) {
				status = s.getStatus();
			}
			if (rictext.equals("")) {
				rictext = s.getRicText();
			}
			alarmKennung += s.getAlarmKennung() + " ";
		}

		con = null;
		PreparedStatement stmt3 = null;
		try {
			con = conPool.getConnectionFromPool();
			stmt3 = con
					.prepareStatement("INSERT INTO `alarmierungscache` SET `alarmid`=? , `einsatzart`=?, `uhrzeit`=?, `datum`=?, `feuerwehrname`=?, `alarmkennungen`=?, `status`=?, `rictext`=?, `feuerwehrtype`=?, `timestamp`=?, `debug`=?");

			stmt3.setInt(1, alarmid);
			stmt3.setString(2, einsatzArt);
			stmt3.setString(3, uhrzeit);
			stmt3.setString(4, datum);
			stmt3.setString(5, getFeuerwehrNameByID(feuerwehrId));
			stmt3.setString(6, alarmKennung);
			stmt3.setString(7, status);
			stmt3.setString(8, rictext);
			stmt3.setString(9, getFeuerwehrTypeByID(feuerwehrId));
			stmt3.setTimestamp(10, new Timestamp(alarmTimestamp));
			stmt3.setBoolean(11, insertAsDebug(feuerwehrId));			
			stmt3.executeUpdate();
			stmt3.close();
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt3.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conPool.returnConnectionToPool(con);
		}
//		long time5 = System.nanoTime();
//		System.out.println("gesammt " + (time5-time1)/1000);
//		System.out.println("2-1 " + (time2-time1)/1000);
//		System.out.println("3-2 " + (time3-time2)/1000);
//		System.out.println("4-3 " + (time4-time3)/1000);
//		System.out.println("5-4 " + (time5-time4)/1000);
//		System.out.println();
	}

	public static void removeDebugAlarme() {

		Connection con = null;
		PreparedStatement stmt1 = null;
		try {

			con = conPool.getConnectionFromPool();
			stmt1 = con.prepareStatement("DELETE FROM alarmierungscache WHERE debug = ?");

			stmt1.setInt(1, 1);
			stmt1.executeUpdate();
			stmt1.close();
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {

			try {
				stmt1.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conPool.returnConnectionToPool(con);
		}
		
		try {

			con = conPool.getConnectionFromPool();
			stmt1 = con.prepareStatement("DELETE FROM alarmierungen WHERE debug = ?");

			stmt1.setInt(1, 1);
			stmt1.executeUpdate();
			stmt1.close();
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {

			try {
				stmt1.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conPool.returnConnectionToPool(con);
		}
	}

	private static boolean isAlarmKennungSchonEingegangen(int alarmid, String alarmKennung) {

		Connection con = null;
		PreparedStatement stmt1 = null;
		try {

			con = conPool.getConnectionFromPool();
			stmt1 = con.prepareStatement("SELECT id FROM alarmierteschleifen WHERE alarmid = ? AND alarmkennung = ?  AND UNIX_TIMESTAMP(timestamp) > UNIX_TIMESTAMP(now()) - 900");

			stmt1.setInt(1, alarmid);
			stmt1.setString(2, alarmKennung);
			ResultSet rs = stmt1.executeQuery();
			
			while (rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt1.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conPool.returnConnectionToPool(con);
		}
		return false;
	}

	// private static int getFeuerwehrKennungByAlarm(EinsatzAlarm alarm) {
	//
	// String kennung = alarm.getAlarmKennung();
	// String type = alarm.getType();
	//
	// String sql = "SELECT id FROM feuerwehrkennungen WHERE value = '" +
	// kennung
	// + "' AND type = '" + type + "'";
	// Statement stmt;
	// ResultSet rs;
	// try {
	// stmt = con.createStatement();
	// rs = stmt.executeQuery(sql);
	//
	// while (rs.next()) {
	//
	// return rs.getInt("id");
	// }
	// } catch (SQLException e) {
	// // TODO Auto-generated catch block
	// e.printStackTrace();
	// }
	// return -1;
	// }

	public static List<JProwlInfo> getProwlAlarmInfos(EinsatzAlarm alarm) {

		String feuerwehrId = alarm.getFeuerwehrID() + "";
		List<JProwlInfo> list = new ArrayList<JProwlInfo>();
		String sql = "SELECT prioritaet, userid FROM prowlzuordnung WHERE feuerwehrid = '"
				+ feuerwehrId + "' AND " + "status='aktive'";
		Connection con = null;
		Statement stmt = null;
		try {

			con = conPool.getConnectionFromPool();
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String userid = rs.getString("userid");
				String prowlId = getProwlidByUserId(userid);
				String prioritaet = rs.getString("prioritaet");
				if (!prowlId.equals("")) {
					list.add(new JProwlInfo(prowlId, prioritaet));
				}
			}
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conPool.returnConnectionToPool(con);
		}
		return list;
	}

	public static List<NotifryInfo> getNotifryInfos(EinsatzAlarm alarm) {

		String feuerwehrId = alarm.getFeuerwehrID() + "";
		List<NotifryInfo> list = new ArrayList<NotifryInfo>();
		String sql = "SELECT prioritaet, userid FROM prowlzuordnung WHERE feuerwehrid = '"
				+ feuerwehrId + "' AND " + "status='aktive'";
		Connection con = null;
		Statement stmt = null;
		try {

			con = conPool.getConnectionFromPool();
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String userid = rs.getString("userid");
				String notifryKey = getNotifryKeyByUserId(userid);
				if (!notifryKey.equals("")) {
					list.add(new NotifryInfo(notifryKey));
				}
			}
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conPool.returnConnectionToPool(con);
		}
		return list;
	}

	/**
	 * @param schleife
	 * @return
	 */
	public static String getFeuerwehrNameByID(int id) {

		String sql = "SELECT name FROM feuerwehren WHERE id = '" + id + "'";
		Connection con = null;
		Statement stmt = null;
		try {

			con = conPool.getConnectionFromPool();
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				return rs.getString("name");
			}
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conPool.returnConnectionToPool(con);
		}
		return "";
	}
	
	public static String getFeuerwehrTypeByID(int id) {

		String sql = "SELECT type FROM feuerwehren WHERE id = '" + id + "'";
		Connection con = null;
		Statement stmt = null;
		try {

			con = conPool.getConnectionFromPool();
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				return rs.getString("type");
			}
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conPool.returnConnectionToPool(con);
		}
		return "Unbekannt";
	}

	/**
	 * 
	 * @param schleife
	 * @return
	 */

	public static int getFeuerwehrIdByAlarmValue(String schleife) {

		String sql = "SELECT feuerwehrid FROM feuerwehrkennungen WHERE value = '" + schleife + "'";
		Connection con = null;
		Statement stmt = null;
		try {

			con = conPool.getConnectionFromPool();
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				return rs.getInt("feuerwehrid");
			}
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conPool.returnConnectionToPool(con);
		}
		return -1;
	}

	/**
	 * 
	 * @param schleife
	 * @return
	 */
	public static String getProwlidByUserId(String userid) {

		String sql = "SELECT prowlid FROM user WHERE id = '" + userid + "' AND prowl = '1'";
		Connection con = null;
		Statement stmt = null;
		try {

			con = conPool.getConnectionFromPool();
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				return rs.getString("prowlid");
			}
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conPool.returnConnectionToPool(con);
		}
		return "";
	}

	/**
	 * 
	 * @param schleife
	 * @return
	 */
	public static String getNotifryKeyByUserId(String userid) {

		String sql = "SELECT notifryKey FROM user WHERE id = '" + userid + "' AND notifry = '1'";
		Connection con = null;
		Statement stmt = null;
		try {

			con = conPool.getConnectionFromPool();
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				return rs.getString("notifryKey");
			}
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conPool.returnConnectionToPool(con);
		}
		return "";
	}

	/**
	 * @return
	 */
	public static List<String> getAlleFeuerwehrIDs() {

		List<String> list = new ArrayList<String>();
		String sql = "SELECT id FROM feuerwehren";
		Connection con = null;
		Statement stmt = null;
		try {

			con = conPool.getConnectionFromPool();
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				list.add(rs.getString("id"));
			}
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conPool.returnConnectionToPool(con);
		}
		return list;
	}

	public static void setNumberInValid(SMSAlamierung smsAlarmierung) {

		String sql = "UPDATE user SET nummerchecked = '0' WHERE id = '"
				+ smsAlarmierung.getUserId() + "'";
		Connection con = null;
		Statement stmt = null;
		try {

			con = conPool.getConnectionFromPool();
			stmt = con.createStatement();
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conPool.returnConnectionToPool(con);
		}
	}

	/**
	 * @param fmsSchleife
	 * @return
	 */
	public static boolean getIstFMSFeuerwehrByID(int id) {

		String sql = "SELECT fms FROM feuerwehren WHERE id = '" + id + "'";
		Connection con = null;
		Statement stmt = null;
		try {

			con = conPool.getConnectionFromPool();
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				return rs.getBoolean("fms");
			}
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conPool.returnConnectionToPool(con);
		}
		return false;
	}

	public static boolean getIst5TonFeuerwehrByID(int id) {

		String sql = "SELECT 5ton FROM feuerwehren WHERE id = '" + id + "'";
		Connection con = null;
		Statement stmt = null;
		try {

			con = conPool.getConnectionFromPool();
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				return rs.getBoolean("5ton");
			}
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conPool.returnConnectionToPool(con);
		}
		return false;
	}

	public static boolean getIstPocsagFeuerwehrByID(int id) {

		String sql = "SELECT pocsag FROM feuerwehren WHERE id = '" + id + "'";
		Connection con = null;
		Statement stmt = null;
		try {

			con = conPool.getConnectionFromPool();
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				return rs.getBoolean("pocsag");
			}
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conPool.returnConnectionToPool(con);
		}
		return false;
	}

	public static void setIstFMSFeuerwehr(int id) {

		String sql = "UPDATE feuerwehren SET fms = '1' WHERE id = '" + id + "'";
		Connection con = null;
		Statement stmt = null;
		try {

			con = conPool.getConnectionFromPool();
			stmt = con.createStatement();
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conPool.returnConnectionToPool(con);
		}
	}

	private static String getAlarmType(String alarmKennung) {

		Connection con = null;
		PreparedStatement stmt = null;
		try {

			con = conPool.getConnectionFromPool();
			stmt = con.prepareStatement("SELECT type FROM `feuerwehrkennungen` WHERE value = ?");

			stmt.setString(1, alarmKennung);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {

				return rs.getString("type");
			}
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conPool.returnConnectionToPool(con);
		}
		return "unknown";
	}

	public ConnectionPool getConPool() {
		return conPool;
	}

}
